# 导入相关库和模块
from models.sys_role_depts import SysRoleDept
from config.logging import logger
from services.database_manager import databaseManager
from models.sys_users import SysUser, SysUserSchema
from sqlalchemy import and_, or_
import re
import asyncio
from services.auth_handler import AuthHandler
from datetime import datetime
from models.sys_depts import SysDept
from sqlalchemy.orm import joinedload
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from config.mysql_settings import AIO_DATABASE_URL
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError
from models.sys_roles import SysRole
from models.sys_user_roles import SysUserRole
from models.sys_user_posts import SysUserPost
from models.sys_posts import SysPost
from sqlalchemy.future import select
from sqlalchemy.sql import func


engine = create_async_engine(AIO_DATABASE_URL, echo=True)

AsyncSessionLocal = sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False
)
# 定义服务类
class SysAdminApiService:
    def __init__(self, db):
        self.db = db
        self.db_manager = databaseManager(self.db)

    async def sys_admin_list(self, page: int, limit: int, email: str, userName: str, searchData: dict):
        """
        系统管理员列表
        """
        async with AsyncSessionLocal() as session:
            self.db_manager.db = session
            async with session.begin():
                try:
                    # 初始化查询
                    query = select(SysUser).options(joinedload(SysUser.dept))
                    filters = []
                    filters = [SysUser.deleted_at == None]  # 确保只查询未删除的记录
                    if userName:
                        filters.append(SysUser.user_name == userName)
                    if email:
                        filters.append(SysUser.email == email)
                    if searchData:
                        if 'userName' in searchData:
                            filters.append(SysUser.user_name == searchData['userName'])
                        if 'status' in searchData:
                            filters.append(SysUser.status == searchData['status'])
                        if 'phoneNumber' in searchData:
                            filters.append(SysUser.phone_number == searchData['phoneNumber'])
                        if 'startTime' in searchData and 'endTime' in searchData:
                            filters.append(SysUser.created_at.between(searchData['startTime'], searchData['endTime']))

                    # 应用过滤条件
                    if filters:
                        query = query.where(and_(*filters))

                    # 获取总数
                    total_count_query = select(func.count(SysUser.id)).where(and_(*filters))
                    total_count_result = await session.execute(total_count_query)
                    total_count = total_count_result.scalar()

                    # 获取分页数据
                    result = await session.execute(query.offset((page - 1) * limit).limit(limit))
                    users = result.scalars().all()

                    res = []
                    for user in users:
                        user_info = self.db_manager.convert_dict_keys(user.to_dict())

                        # 获取部门信息
                        dept_info = await session.execute(select(SysDept).where(SysDept.id == user.dept_id))
                        dept = dept_info.scalars().first()
                        user_info["dept"] = self.db_manager.convert_dict_keys(dept.to_dict()) if dept else {}

                        # 获取角色信息
                        role_mappings = await session.execute(select(SysUserRole).where(SysUserRole.user_id == user.id))
                        role_ids = [role_mapping.role_id for role_mapping in role_mappings.scalars().all()]
                        if role_ids:
                            roles = await session.execute(select(SysRole).where(SysRole.id.in_(role_ids)))
                            user_info["roles"] = [role.to_dict() for role in roles.scalars().all()]
                        else:
                            user_info["roles"] = []

                        # 获取岗位信息
                        post_mappings = await session.execute(select(SysUserPost).where(SysUserPost.user_id == user.id))
                        post_ids = [post_mapping.post_id for post_mapping in post_mappings.scalars().all()]
                        if post_ids:
                            posts = await session.execute(select(SysPost).where(SysPost.id.in_(post_ids)))
                            user_info["posts"] = [post.to_dict() for post in posts.scalars().all()]
                        else:
                            user_info["posts"] = []

                        user_info.pop("password", None)  # 删除密码字段

                        res.append(user_info)

                    return {"list": res, 'total': total_count}

                except SQLAlchemyError as e:
                    logger.error(f"获取系统管理员列表失败，数据库操作出错: {str(e)}")
                    return {"error": f"获取系统管理员列表失败，数据库操作出错: {str(e)}"}

    async def sys_login(self, userData: dict):
        """
        系统管理员登录
        """
        user = {}
        if re.match(r"^\w+@\w+\.\w+$", userData["userName"]) is not None:
            user["email"] = userData["username"]
        else:
            user["user_name"] = userData["userName"]
        userData.pop("userName")
        # 找出用户信息
        user["status"] = 0
        user["deleted_at"] = None
        info = self.db_manager.detail_query(SysUser, user)
        if info is None:
            return {"error": "用户不存在"}

        if AuthHandler().verify_password(userData["password"], info["password"]) is False:
            return {"error": "账号/密码不匹配"}

        return self.db_manager.convert_dict_keys(self.user_data(self.convert_datetime_fields(info)))

    async def sys_create_user(self, userData: dict) -> dict:
        """
        创建系统管理员
        """
        user_info = userData["data"]["user"]
        role_info = userData["data"]["role"]
        post_info = userData["data"]["post"]

        async with AsyncSessionLocal() as session:
            # 检查用户名或邮箱是否存在
            existing_user = await session.execute(
                select(SysUser).filter(
                    or_(SysUser.user_name == user_info["userName"], SysUser.email == user_info["email"])
                )
            )
            if existing_user.scalars().first():
                return {"error": "用户名或邮箱已存在"}

        # 哈希密码
        user_info["password"] = AuthHandler().get_password_hash(user_info["password"])
        user_info = self.db_manager.snake_convert_dict_keys(user_info)

        # 创建用户记录
        info = await asyncio.get_event_loop().run_in_executor(
            None, self.db_manager.create_entry, SysUser, user_info
        )
        if not info or 'password' not in info:
            return {"error": "用户创建失败，数据库操作未成功"}

        info.pop("password")
        info["token"] = AuthHandler().create_access_token(info, 24)
        async with AsyncSessionLocal() as session:
            self.db_manager.db = session
            async with session.begin():
                try:

                    # 处理用户角色、岗位和部门
                    await self._assign_roles_posts_dept(session, role_info, user_info["dept_id"], post_info, info["id"])

                    # 获取完整的用户信息
                    full_user_info = await self._get_full_user_info(info["id"])
                    return full_user_info

                except SQLAlchemyError as e:
                    await session.rollback()
                    logger.error(f"用户创建失败，数据库操作出错: {str(e)}")
                    return {"error": f"用户创建失败，数据库操作出错: {str(e)}"}

    async def sys_update_user(self, userData: dict, userId):
        """
        更新系统管理员
        """
        user_info = userData["data"]["user"]
        role_info = userData["data"]["role"]
        post_info = userData["data"]["post"]

        async with AsyncSessionLocal() as session:
            self.db_manager.db = session
            try:
                # 更新用户信息
                user_info["updateBy"] = userId
                user_info["updateTime"] = datetime.now()
                user_info = self.db_manager.snake_convert_dict_keys(user_info)

                updated_user = await self.db_manager.async_detail_update(SysUser, {"id": userId}, user_info)

                if not updated_user:
                    return {"error": "用户更新失败，数据库操作未成功"}

                # 更新用户角色和岗位
                await self._assign_roles_posts_dept(session, role_info, user_info["dept_id"], post_info, userId)

                # 获取完整的用户信息
                full_user_info = await self._get_full_user_info(userId)
                return full_user_info

            except SQLAlchemyError as e:
                await session.rollback()
                logger.error(f"用户更新失败，数据库操作出错: {str(e)}")
                return {"error": f"用户更新失败，数据库操作出错: {str(e)}"}

    async def sys_delete_user(self, userId: int):
        """
        删除系统管理员
        """
        self.db_manager.detail_update(SysUser, {"id": userId}, {"deleted_at": datetime.now().strftime("%Y-%m-%d %H:%M:%S")})
        return True

    async def sys_detail_user(self, userId: int):
        """
        系统管理员详情
        """
        return await self._get_full_user_info(userId)

    @staticmethod
    def user_data(userData):
        """
        生成token和获取用户信息
        :param userData:
        :return:
        """
        userData.pop("password") if 'password' in userData.keys() else None
        userData["token"] = AuthHandler().create_access_token(userData, 24)
        return userData

    @staticmethod
    def convert_datetime_fields(info: dict):
        """
        把datetime转换
        :param info:
        :return:
        """
        for key, value in info.items():
            if isinstance(value, datetime):
                info[key] = value.isoformat()
        return info

    async def sys_user_info(self, userId: int):
        """
        获取当前登录用户信息
        """
        return await self._get_full_user_info(userId)


    async def _assign_roles_and_posts(self, session: AsyncSession, userData: dict, user_id: int):
        """
        处理用户角色和岗位的分配
        """
        role_ids = userData.get("roleIds", [])
        post_ids = userData.get("postIds", [])

        # 分配角色
        for role_id in role_ids:
            user_role = SysUserRole(user_id=user_id, role_id=role_id)
            session.add(user_role)

        # 分配岗位
        for post_id in post_ids:
            user_post = SysUserPost(user_id=user_id, post_id=post_id)
            session.add(user_post)

        await session.commit()

    async def _assign_roles_posts_dept(self, session: AsyncSession, role_info: dict, deptId: int, post_info: dict, user_id: int):
        """
            处理用户角色、岗位和部门的分配
            """
        role_id = role_info["roleId"]
        post_id = post_info["postId"]
        dept_id = deptId

        try:
            # 使用 no_autoflush 避免自动刷新引发的错误
            with session.no_autoflush:
                # 分配角色前检查是否已经存在
                existing_user_role = await session.execute(
                    select(SysUserRole).filter_by(user_id=user_id, role_id=role_id)
                )
                if not existing_user_role.scalars().first():
                    user_role = SysUserRole(user_id=user_id, role_id=role_id)
                    session.add(user_role)

                # 分配岗位前检查是否已经存在
                existing_user_post = await session.execute(
                    select(SysUserPost).filter_by(user_id=user_id, post_id=post_id)
                )
                if not existing_user_post.scalars().first():
                    user_post = SysUserPost(user_id=user_id, post_id=post_id)
                    session.add(user_post)

                # 分配部门前检查是否已经存在
                existing_role_dept = await session.execute(
                    select(SysRoleDept).filter_by(role_id=role_id, dept_id=dept_id)
                )
                if not existing_role_dept.scalars().first():
                    role_dept = SysRoleDept(role_id=role_id, dept_id=dept_id)
                    session.add(role_dept)

                await session.commit()

        except SQLAlchemyError as e:
            await session.rollback()
            logger.error(f"角色或岗位分配失败，数据库操作出错: {str(e)}")
            raise


    async def _get_full_user_info(self, user_id: int) -> dict:
        """
        获取完整的用户信息，包括部门、角色和岗位详细信息
        """
        async with AsyncSessionLocal() as session:
            self.db_manager.db = session
            async with session.begin():
                try:
                    # 获取用户基本信息
                    user_info = await self.db_manager.async_detail_query(SysUser, {"id": user_id}, need=True)
                    if not user_info:
                        return {"error": "用户ID不存在"}

                    user_info = self.db_manager.convert_dict_keys(user_info)

                    # 获取部门信息
                    dept_info = await self.db_manager.async_detail_query(SysDept, {"id": user_info["deptId"]}, need=True)
                    user_info["dept"] = self.db_manager.convert_dict_keys(dept_info)

                    # 获取角色信息
                    role_mappings = await self.db_manager.async_list_query(SysUserRole, {"user_id": user_id}, needRow=True)
                    role_ids = [role_mapping.role_id for role_mapping in role_mappings]
                    roles = await self.db_manager.async_list_query(SysRole, {"id": role_ids}, needRow=True)
                    logger.info(f"查询一下{user_id}")
                    user_info["roleIds"] = role_ids
                    user_info["roles"] = [role.to_dict() for role in roles]

                    # 获取岗位信息
                    post_mappings = await self.db_manager.async_list_query(SysUserPost, {"user_id": user_id}, needRow=True)
                    post_ids = [post_mapping.post_id for post_mapping in post_mappings]
                    posts = await self.db_manager.async_list_query(SysPost, {"id": post_ids}, needRow=True)
                    user_info["postIds"] = post_ids
                    user_info["posts"] = [post.to_dict() for post in posts]
                    user_info.pop("password") if "password" in user_info else None
                    logger.info(f"++{user_info}")
                    return user_info

                except SQLAlchemyError as e:
                    logger.error(f"获取用户详细信息失败，数据库操作出错: {str(e)}")
                    return {"error": f"获取用户详细信息失败，数据库操作出错: {str(e)}"}
